// @Author : zaniu(zzaniu@126.com)
// @Time   : 2021/9/1 19:02
// @Desc   :
//
//                   _ooOoo_
//                  o8888888o
//                  88" . "88
//                  (| -_- |)
//                  O\  =  /O
//               ____/`---'\____
//             .'  \\|     |//  `.
//            /  \\|||  :  |||//  \
//           /  _||||| -:- |||||-  \
//           |   | \\\  -  /// |   |
//           | \_|  ''\---/''  |   |
//           \  .-\__  `-`  ___/-. /
//         ___`. .'  /--.--\  `. . __
//      ."" '<  `.___\_<|>_/___.'  >'"".
//     | | :  `- \`.;`\ _ /`;.`/ - ` : | |
//     \  \ `-.   \_ __\ /__ _/   .-` /  /
// =====`-.____`-.___\_____/___.-`____.-'=====
//                   `=---='
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//           佛祖保佑       永无BUG
//
package utils

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"regexp"
	"runtime/debug"
	"strconv"
	"strings"
	"time"
)

var db *sql.DB
var selectRegexp = regexp.MustCompile("(?i)insert") // (?i) 忽略大小写匹配

type dbData []map[string]interface{}

func (d dbData) first() map[string]interface{} {
	if len(d) > 0 {
		return d[0]
	}
	// 返回nil或者直接报错？ 对go还是不熟啊
	return nil
}

type mySqlInterface interface {
	exec(sqlStr string, args ...interface{}) (sql.Result, error)
	query(sqlStr string, args ...interface{}) (*sql.Rows, error)
}

type Sql struct {
	mysqlDb *sql.DB
}

type Tx struct {
	tx *sql.Tx
}

func NewSql() *Sql {
	var err error
	db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local", GlobalServerConfig.SqlUserName, GlobalServerConfig.SqlUserPwd, GlobalServerConfig.SqlHost, GlobalServerConfig.SqlPort, GlobalServerConfig.SqlDbName))
	if err != nil {
		fmt.Println("数据库连接失败")
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库连接失败")
		panic(err)
	}
	db.SetMaxIdleConns(1) // 最大连接数
	db.SetMaxOpenConns(0) // 最大闲置连接
	return &Sql{db}
}

func setWhereParam(where *map[string]interface{}, tmpStr *[]string, tmpArgs *[]interface{}) {
	for k, v := range *where {
		if strings.HasSuffix(k, "__gt") {
			*tmpStr = append(*tmpStr, strings.Replace(k, "__gt", "", 1)+">?")
		} else {
			if strings.HasSuffix(k, "__lt") {
				*tmpStr = append(*tmpStr, strings.Replace(k, "__lt", "", 1)+"<?")
			} else {
				if strings.HasSuffix(k, "__in") {
					length := len(v.([]interface{}))
					tmp := make([]string, length, length)
					for i := 0; i < length; i++ {
						tmp[i] = "?"
					}
					*tmpStr = append(*tmpStr, strings.Replace(k, "__in", "", 1)+" in ("+strings.Join(tmp, ",")+")")
				} else {
					*tmpStr = append(*tmpStr, k+"=?")
				}
			}
		}
		switch v.(type) {
		case []interface{}:
			for _, d := range v.([]interface{}) {
				*tmpArgs = append(*tmpArgs, d)
			}
		default:
			*tmpArgs = append(*tmpArgs, v)
		}
	}
}

func (s Sql) exec(sqlStr string, args ...interface{}) (sql.Result, error) {
	return s.mysqlDb.Exec(sqlStr, args...)
}

func (s Sql) query(sqlStr string, args ...interface{}) (*sql.Rows, error) {
	return s.mysqlDb.Query(sqlStr, args...)
}

func (s Sql) Begin() Tx {
	tx, err := s.mysqlDb.Begin()
	if err != nil {
		panic(err)
	}
	return Tx{tx}
}

func (s Sql) Select(table string, field string, where map[string]interface{}, limit int) *dbData {
	var sqlStr string
	tmpStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	setWhereParam(&where, &tmpStr, &tmpArgs)
	if limit <= 0 {
		sqlStr = "select " + field + " from " + table + " where " + strings.Join(tmpStr, " and ")
	} else {
		sqlStr = "select " + field + " from " + table + " where " + strings.Join(tmpStr, " and ") + " limit " + strconv.Itoa(limit)
	}
	return s.RawSelect(sqlStr, tmpArgs...)
}

func (s Sql) Update(table string, fieldAndValue map[string]interface{}, where map[string]interface{}) int {
	if len(where) == 0 {
		panic(errors.New("更新必须带where参数"))
	}
	var sqlStr string
	fieldStr := make([]string, 0)
	whereStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	for k, v := range fieldAndValue {
		fieldStr = append(fieldStr, k+"=?")
		tmpArgs = append(tmpArgs, v)
	}
	setWhereParam(&where, &whereStr, &tmpArgs)
	sqlStr = "update " + table + " set " + strings.Join(fieldStr, ", ") + " where " + strings.Join(whereStr, " and ")
	return s.RawSql(sqlStr, tmpArgs...)
}

func (s Sql) Delete(table string, where map[string]interface{}) int {
	if len(where) == 0 {
		panic(errors.New("删除必须带where参数"))
	}
	var sqlStr string
	whereStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	setWhereParam(&where, &whereStr, &tmpArgs)
	sqlStr = "delete from " + table + " where " + strings.Join(whereStr, " and ")
	return s.RawSql(sqlStr, tmpArgs...)
}

// 如果数据库数据超级多的话，可能int会溢出，这个时候可能就要用int64(sql自带的就是int64)
func (s Sql) Insert(table string, column []string, values []interface{}) int {
	var sqlStr string
	if len(column) != len(values) {
		panic(errors.New("字段与值长度不一致"))
	}
	length := len(values)
	tmp := make([]string, length, length)
	for i := 0; i < length; i++ {
		tmp[i] = "?"
	}
	sqlStr = "insert into " + table + "(" + strings.Join(column, ",") + ")" + " values (" + strings.Join(tmp, ",") + ")"
	fmt.Println("sqlStr = ", sqlStr)
	return s.RawSql(sqlStr, values...)
}

func (s Sql) RawSelect(sqlStr string, args ...interface{}) *dbData {
	return rawSelect(s, sqlStr, args...)
}

func (s Sql) RawDelete(sqlStr string, args ...interface{}) int {
	return rawSql(s, sqlStr, args...)
}

func (s Sql) RawInsert(sqlStr string, values ...interface{}) int {
	return rawSql(s, sqlStr, values...)
}

func (s Sql) RawSql(sqlStr string, args ...interface{}) int {
	return rawSql(s, sqlStr, args...)
}

func (s Sql) Close() {
	err := s.mysqlDb.Close()
	if err != nil {
		fmt.Println("关闭sql失败...")
		debug.PrintStack()
		panic(err)
	}
}

func (tx Tx) exec(sqlStr string, args ...interface{}) (sql.Result, error) {
	return tx.tx.Exec(sqlStr, args...)
}

func (tx Tx) query(sqlStr string, args ...interface{}) (*sql.Rows, error) {
	return tx.tx.Query(sqlStr, args...)
}

func (tx Tx) Select(table string, field string, where map[string]interface{}, limit int) *dbData {
	var sqlStr string
	tmpStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	setWhereParam(&where, &tmpStr, &tmpArgs)
	if limit <= 0 {
		sqlStr = "select " + field + " from " + table + " where " + strings.Join(tmpStr, " and ")
	} else {
		sqlStr = "select " + field + " from " + table + " where " + strings.Join(tmpStr, " and ") + " limit " + strconv.Itoa(limit)
	}
	return tx.RawSelect(sqlStr, tmpArgs...)
}

func (tx Tx) Update(table string, fieldAndValue map[string]interface{}, where map[string]interface{}) int {
	if len(where) == 0 {
		panic(errors.New("更新必须带where参数"))
	}
	var sqlStr string
	fieldStr := make([]string, 0)
	whereStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	for k, v := range fieldAndValue {
		fieldStr = append(fieldStr, k+"=?")
		tmpArgs = append(tmpArgs, v)
	}
	setWhereParam(&where, &whereStr, &tmpArgs)
	sqlStr = "update " + table + " set " + strings.Join(fieldStr, ", ") + " where " + strings.Join(whereStr, " and ")
	return tx.RawSql(sqlStr, tmpArgs...)
}

func (tx Tx) Delete(table string, where map[string]interface{}) int {
	if len(where) == 0 {
		panic(errors.New("删除必须带where参数"))
	}
	var sqlStr string
	whereStr := make([]string, 0)
	tmpArgs := make([]interface{}, 0)
	setWhereParam(&where, &whereStr, &tmpArgs)
	sqlStr = "delete from " + table + " where " + strings.Join(whereStr, " and ")
	return tx.RawSql(sqlStr, tmpArgs...)
}

// 如果数据库数据超级多的话，可能int会溢出，这个时候可能就要用int64(sql自带的就是int64)
func (tx Tx) Insert(table string, column []string, values []interface{}) int {
	var sqlStr string
	if len(column) != len(values) {
		panic(errors.New("字段与值长度不一致"))
	}
	length := len(values)
	tmp := make([]string, length, length)
	for i := 0; i < length; i++ {
		tmp[i] = "?"
	}
	sqlStr = "insert into " + table + "(" + strings.Join(column, ",") + ")" + " values (" + strings.Join(tmp, ",") + ")"
	return tx.RawSql(sqlStr, values...)
}

func (tx Tx) RawSelect(sqlStr string, args ...interface{}) *dbData {
	return rawSelect(tx, sqlStr, args...)
}

func (tx Tx) RawDelete(sqlStr string, args ...interface{}) int {
	return rawSql(tx, sqlStr, args...)
}

func (tx Tx) RawInsert(sqlStr string, values ...interface{}) int {
	return rawSql(tx, sqlStr, values...)
}

func (tx Tx) RawSql(sqlStr string, values ...interface{}) int {
	return rawSql(tx, sqlStr, values...)
}

func (tx Tx) Rollback() {
	err := tx.tx.Rollback()
	if err != nil {
		panic(err)
	}
}

func (tx Tx) Commit() {
	err := tx.tx.Commit()
	if err != nil {
		tx.tx.Rollback()
		panic(err)
	}
}

func rawSelect(s mySqlInterface, sqlStr string, args ...interface{}) *dbData {
	rows, err := s.query(sqlStr, args...)
	if err != nil {
		fmt.Printf("Query failed,err:%v\n", err)
		debug.PrintStack()
		panic(err)
	}
	defer func() {
		if rows != nil {
			rows.Close() // 关闭掉未scan的sql连接
		}
	}()
	columns, err := rows.Columns()
	if err != nil {
		debug.PrintStack()
		panic(err)
	}

	// columnTypes, _ := rows.ColumnTypes()
	// for _, d := range columnTypes {
	// 	fmt.Println("columnTypes = ", d.Name())
	// 	rT := d.ScanType()
	// 	fmt.Println("columnTypes = ", rT)  // 返回的是反射类型
	// 	fmt.Println("rT.Name() = ", rT.Name())
	// 	length, ok:= d.Length()
	// 	if ok {
	// 		fmt.Println("length = ", length)
	// 	}
	// }

	cache := make([]interface{}, len(columns))
	for index, _ := range cache {
		cache[index] = new(interface{}) // new 返回一个指针
	}
	ret := make(dbData, 0)
	for rows.Next() {
		err := rows.Scan(cache...) // Scan 会释放连接， defer r.rows.Close()
		m := make(map[string]interface{})
		for index, k := range columns {
			m[k] = *cache[index].(*interface{})
			switch m[k].(type) {
			case []byte:
				m[k] = string(m[k].([]byte))
			case time.Time:
				m[k] = m[k].(time.Time).Format("2006-01-02 15:04:05") // 这里其实应该是可以不转的
			case float64:
				m[k] = m[k].(float64)
			}
		}
		ret = append(ret, m)
		if err != nil {
			panic(err)
		}
	}
	return &ret
}

func rawSql(s mySqlInterface, sqlStr string, args ...interface{}) int {
	result, err := s.exec(sqlStr, args...)
	if err != nil {
		panic(err)
	}
	if len(selectRegexp.FindString(sqlStr)) > 0 {
		n, _ := result.LastInsertId()
		return int(n)
	} else {
		n, _ := result.RowsAffected()
		return int(n)
	}
}
